package com.sh.ssao.lottery.config.poi;

import com.sh.ssao.lottery.config.utils.StrUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;

/**
 * excel 导入功能
 * file 数据源文件
 * filed 对应字段
 */
@Component
@Slf4j
public class ImportExcelUtiles {

    public void courseDeclareImport(MultipartFile file) {
        try {
            File f = multipartFileToFile(file);
            //获取工作簿
            Workbook wb = getWorkbook(f);
            //获取sheet页
            int sheetunm = wb.getNumberOfSheets();

            //全局id
            String generatorDate = StrUtil.generatorDate();
            for (int i = 0;i<sheetunm;i++){
               Sheet sheet =  wb.getSheetAt(i);
               int b = sheet.getNumMergedRegions();
                System.out.println("总行数："+b);
               int count = 0;
                for (Row row : sheet) {
                    //跳过头几行
                    if(count < 2 ) {
                        count++;
                        continue;
                    }

                    //获取总列数(空格的不计算)
                    int columnTotalNum = row.getPhysicalNumberOfCells();
                    System.out.println("总列数：" + columnTotalNum);
                    System.out.println("最大列数：" + row.getLastCellNum());

                    //生成id
                    String id = "CD"+generatorDate + String.format("%04d",count);
                    int end = row.getLastCellNum();
                    //获取数据并组装对象数据
                    for (int j = 0; j < end; j++) {

                        Cell cell = row.getCell(j);
                        if (cell == null) {
                            System.out.print("null" + "\t");
                            continue;
                        }
                        Object obj = getValue(cell);
                        System.out.println(j+":"+obj);
                        if (null==obj||(null != obj && "".equals((String.valueOf(cell)).trim()))){
                            //导入结束，退出....
                            System.out.println("导入结束，退出...");
                            return ;
                        }
                    }
                    count++;//记录行数
                }
            }

            //删除文件
            if (f.exists()) {
                f.delete();
            }
            log.debug("导入数据结束....");
        } catch (Exception e) {
            e.printStackTrace();
            log.error("导入数据有出错",e);
        }
    }

    /**
     * 智选系列课程导入
     * @param file
     */
    public void courseDailyImport(MultipartFile file) {
        try {
            File f = multipartFileToFile(file);
            //获取工作簿
            Workbook wb = getWorkbook(f);
            //获取sheet页
            int sheetunm = wb.getNumberOfSheets();

            //全局id
            String generatorDate = StrUtil.generatorDate();
            for (int i = 0;i<sheetunm;i++){
                Sheet sheet =  wb.getSheetAt(i);
                int b = sheet.getNumMergedRegions();
                System.out.println("总行数："+b);
                int count = 0;
                for (Row row : sheet) {
                    //跳过头几行
                    if(count < 2 ) {
                        count++;
                        continue;
                    }

                    //获取总列数(空格的不计算)
                    int columnTotalNum = row.getPhysicalNumberOfCells();
                    System.out.println("总列数：" + columnTotalNum);
                    System.out.println("最大列数：" + row.getLastCellNum());

                    //生成id
                    String id = "CY"+generatorDate + String.format("%04d",count);
                    int end = row.getLastCellNum();
                    //获取数据并组装对象数据
                    for (int j = 0; j < end; j++) {

                        Cell cell = row.getCell(j);
                        if (cell == null) {
                            System.out.print("null" + "\t");
                            continue;
                        }
                        Object obj = getValue(cell);
                        System.out.println(j+":"+obj);
                        if (null==obj||(null != obj && "".equals((String.valueOf(cell)).trim()))){
                            //导入结束，退出....
                            System.out.println("导入结束，退出...");
                            return ;
                        }
                    }
                    count++;//记录行数
                }
            }
            /*for (CourseYearBean cd:courseYearBeans) {
                System.out.println(cd.toString());
            }*/

            //删除文件
            if (f.exists()) {
                f.delete();
            }
            log.debug("导入数据结束....");
        } catch (Exception e) {
            e.printStackTrace();
            log.error("导入数据有出错",e);
        }
    }

    /**
     * 获取workbook
     *
     * @return
     */
    private Workbook getWorkbook(File file){
        Workbook wb = null;
        try {
            FileInputStream fis = new FileInputStream(file);
            return new XSSFWorkbook(fis);
        }catch (IOException e){
            log.error("导入数据出错",e);
            return wb;
        }
    }

    /**
     * int CELL_TYPE_NUMERIC = 0;
     * int CELL_TYPE_STRING = 1;
     * int CELL_TYPE_FORMULA = 2;
     * int CELL_TYPE_BLANK = 3;
     * int CELL_TYPE_BOOLEAN = 4;
     * int CELL_TYPE_ERROR = 5;
     * @param
     * @return
     */
    private Object getValue(Cell cell) {
        Object obj = null;
        switch (cell.getCellType()) {
            case BOOLEAN:
                obj = cell.getBooleanCellValue();
                break;
            case ERROR:
                obj = cell.getErrorCellValue();
                break;
            case NUMERIC:
                obj = cell.getNumericCellValue();
                break;
            case STRING:
                obj = cell.getStringCellValue();
                break;
            default:
                break;
        }
        return obj;
    }

    public static File multipartFileToFile(MultipartFile file) throws Exception {
        File toFile = null;
        if (file.equals("") || file.getSize() <= 0) {
            file = null;
        } else {
            InputStream ins = null;
            ins = file.getInputStream();
            toFile = new File(file.getOriginalFilename());
            inputStreamToFile(ins, toFile);
            ins.close();
        }
        return toFile;
    }

    public static void inputStreamToFile(InputStream ins,File file) {
        try {
            OutputStream os = new FileOutputStream(file);
            int bytesRead = 0;
            byte[] buffer = new byte[8192];
            while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
                os.write(buffer, 0, bytesRead);
            }
            os.close();
            ins.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

//    public static void main(String[] args) {
//        File file = new File("D:/批量查询导出数据结果—天眼查(W20071628711563243877097) - 副本.xlsx");
//        File file = new File("D:/credit.xlsx");
//        new ImportExcelUtiles().courseDeclareImport(MultipartFile file);
//    }
}
